package se2;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.GregorianCalendar;
import java.util.Iterator;
import java.util.List;

public class Bill {

	private final String ID = "id";
	private final String PHONE_ACCT_ID = "phone_id";
	private final String START_TIME = "start_time";
	private final String END_TIME = "end_time";
	private final String BALANCE = "balance";
	private final String TOTAL = "total";
	
	public List<CallRecord> crecords = new ArrayList<CallRecord>();
	public String start_time, end_time;
	public GregorianCalendar start_date, end_date;
	public int id;
	public double total, balance;
	
	public PhoneAccount pa;
	
	public void getBill()
	{
		generateBill(start_date,end_date);
	}
	
	public Bill()
	{
	}
	
	public Bill(GregorianCalendar s, GregorianCalendar e, int pid)
	{
		setPhoneAccount(pid);
		generateBill(s,e);
	}
	
	public void generateBill(GregorianCalendar start_date, GregorianCalendar end_date)
	{
		
		this.start_date = start_date;
		this.end_date = end_date;
		
		/* Query DB to extract the callee CallRecords for
		 * the given phone account's extension
		 */
		try
		{
			// DB setup
			Connection conn = getConnection();
			
			PreparedStatement ps;
			ResultSet rs;
	
			ps = conn.prepareStatement("SELECT id FROM CallRecord WHERE caller_id=?");
			ps.setInt(1, pa.getId());
			rs = ps.executeQuery();
			
			while(rs.next())
			{
				int id = rs.getInt(ID);
				//Create call record and add it to the list
				crecords.add(new CallRecord(id));
			}
			rs.close();
			ps.close();
			conn.close();
			
			//Get CallRecords within date range
			Iterator<CallRecord> citer = crecords.iterator();
			while(citer.hasNext())
			{
				CallRecord trec = citer.next();
				if(!(trec.getEndTime().after(start_date) && (trec.getEndTime().before(end_date) || trec.getEndTime().equals(end_date))))
				{
					citer.remove();
				}
			}
		}
		catch(Exception e)
		{
			System.out.println("Error extracting CallRecords");
			System.out.println(e);
		}
	}
	
	public void generateBillById(int bid)
	{
		
		SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		try
		{
			Connection conn = getConnection();
			
			PreparedStatement ps;
			ResultSet rs;
	
			// Check for duplicates by name
			ps = conn.prepareStatement("SELECT * FROM PhoneBill WHERE id = ?");
			ps.setInt(1, bid);
			rs = ps.executeQuery();
			
			if(rs.next())
			{
				//Build Bill
				this.start_date = new GregorianCalendar();
				this.start_date.setTime(df.parse(rs.getString(START_TIME)));
				//start_date.add(GregorianCalendar.MONTH, 1);
				
				this.end_date = new GregorianCalendar();
				this.end_date.setTime(df.parse(rs.getString(END_TIME)));
				//end_date.add(GregorianCalendar.MONTH, 1);
				
				setPhoneAccount(rs.getInt(PHONE_ACCT_ID));
				
				this.setID(rs.getInt(ID));
				this.setTotal(rs.getDouble(TOTAL));
				this.setBalance(rs.getDouble(BALANCE));
				
				//Generate call record list
				generateBill(start_date, end_date);
			}
			
			rs.close();
			ps.close();
			conn.close();
		}
		catch(Exception e)
		{
			System.out.println("Error generating bill by Id");
			System.out.println(e);
		}
	}
	
	public void chargeBill()
	{
		SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		try
		{
			Connection conn = getConnection();
			
			PreparedStatement ps;
	
			String start = df.format(start_date.getTime());
			String end = df.format(end_date.getTime());
			
			// Check for duplicates by name
			ps = conn.prepareStatement(
				"INSERT INTO PhoneBill (phone_id,start_time, end_time, balance, total) VALUES"+
				"(?,datetime(?),datetime(?),?,?)");
			ps.setInt(1, pa.getId());
			ps.setString(2, start);
			ps.setString(3, end);
			ps.setDouble(4, balance);
			ps.setDouble(5, total);
			ps.executeUpdate();
			
			ps.close();
			conn.close();
		
		}
		catch(Exception e)
		{
			System.out.println("Error generating bill by Id");
			System.out.println(e);
		}
	}
		
	public boolean isValidPeriod(GregorianCalendar start, GregorianCalendar end)
	{
		List<Bill> blist = new ArrayList<Bill>();
		blist = getBillsByPhoneAccount();
		
		//Check start is before the end
		if(start.before(end))
			return false;
		
		for(Bill b : blist)
		{
			if (start.before(b.getEnd()) && start.after(b.getStart()))
				return false;
			if (end.before(b.getEnd()) && end.after(b.getStart()))
				return false;
		}
		return true;
	}
	
	public List<Bill> getBillsByPhoneAccount()
	{
		List<Bill> blist = new ArrayList<Bill>();
		
		SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		
		try
		{
			Connection conn = getConnection();
			
			PreparedStatement ps;
			ResultSet rs;
	
			// Check for duplicates by name
			ps = conn.prepareStatement("SELECT * FROM PhoneBill WHERE phone_id = ?");
			ps.setInt(1, pa.getId());
			rs = ps.executeQuery();
	
			while (rs.next())
			{
				
				GregorianCalendar startTime = new GregorianCalendar();
				startTime.setTime(df.parse(rs.getString(START_TIME)));
				//startTime.add(GregorianCalendar.MONTH, 1);
				
				GregorianCalendar endTime = new GregorianCalendar();
				endTime.setTime(df.parse(rs.getString(END_TIME)));
				//endTime.add(GregorianCalendar.MONTH, 1);
				
				int pa_id = rs.getInt(PHONE_ACCT_ID);
				
				//Build Bill
				Bill b = new Bill(startTime, endTime, pa_id);
				b.setID(rs.getInt(ID));
				b.setTotal(rs.getDouble(TOTAL));
				b.setBalance(rs.getDouble(BALANCE));
				blist.add(b);
			}
			
			ps.close();
			rs.close();
			conn.close();
		}
		catch (Exception e)
		{
			System.out.println("Error producing PhoneBill list by phone account id");
			System.out.println(e);
		}
		
		return blist;
	}
	
	public GregorianCalendar getLastBillDate(int id) {
		
		GregorianCalendar latest = new GregorianCalendar();
		latest.setTimeInMillis(0);
		
		try {
			
			Connection conn = getConnection();
			
			PreparedStatement ps;
			ResultSet rs;
	
			ps = conn.prepareStatement("SELECT * FROM PhoneBill WHERE phone_id = ?");
			ps.setInt(1, pa.getId());
			
			rs = ps.executeQuery();
			
			SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
			GregorianCalendar d = new GregorianCalendar();
			
			while (rs.next()){
				d.setTime(df.parse(rs.getString("end_time")));
				if (d.after(latest))
					latest = d;
			}

			rs.close();
			ps.close();
			conn.close();
			
		} catch (Exception e) {
			System.out.println("Error getting last bill date from database.");
			e.printStackTrace();
		}
		return latest;
		
	}
	
	public String getFormattedGenStart()
	{
		SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		return df.format(getLastBillDate(pa.getId()).getTime());
	}
	
	public String getFormattedGenEnd()
	{
		SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		return df.format(new Date());
	}
	
	//Remove this
	public void setCharges()
	{
		pa.setBalance(pa.getBalance()+total);
	}
	
	public void calculateTotal()
	{
		double total = 0;
		for(CallRecord c : crecords)
		{
			total += c.getAmount();
		}
		this.total = total;
	}
	
	public List<CallRecord> getCallRecords()
	{
		return crecords;
	}
	
	public GregorianCalendar getStart()
	{
		return start_date;
	}
	
	public String getFormattedStartDate()
	{
		SimpleDateFormat df = new SimpleDateFormat("EEE, MMM d, yyyy h:mm a");
		return df.format(start_date.getTime());
	}
	
	public String getSortStartDate()
	{
		SimpleDateFormat df = new SimpleDateFormat("yyyyMMddHHmmss");
		return df.format(start_date.getTime());
	}
	
	public GregorianCalendar getEnd()
	{
		return end_date;
	}
	
	public String getFormattedEndDate()
	{
		SimpleDateFormat df = new SimpleDateFormat("EEE, MMM d, yyyy h:mm a");
		return df.format(end_date.getTime());
	}
	
	public String getSortEndDate()
	{
		SimpleDateFormat df = new SimpleDateFormat("yyyyMMddHHmmss");
		return df.format(end_date.getTime());
	}
	
	public String getCallsMade()
	{
		return new Integer(crecords.size()).toString();
	}
	
	public String getTotalAmt()
	{
		DecimalFormat dcf = new DecimalFormat("#0.00");
		dcf.setDecimalSeparatorAlwaysShown(true);
		return "$"+dcf.format(total);
	}
	
	public double getTotal()
	{
		return total;
	}
	
	public String getBalanceAmt()
	{
		return Double.toString(balance);
	}
	
	public double getBalance()
	{
		return balance;
	}
	
	private Connection getConnection() throws ClassNotFoundException, SQLException
	{
		Class.forName("org.sqlite.JDBC");
		String home = System.getProperty("user.home");
		Connection conn = DriverManager.getConnection("jdbc:sqlite:" + home+ "/db/VoIP");
		
		return conn;
	}

	public GregorianCalendar getEndDate() {
		return end_date;
	}

	public void setEndDate(GregorianCalendar end_date) {
		this.end_date = end_date;
	}

	public PhoneAccount getPhoneAccount() {
		return pa;
	}

	public void setPhoneAccount(int pa_id) {
		this.pa = new PhoneAccount(pa_id);
	}

	public GregorianCalendar getStartDate() {
		return start_date;
	}

	public void setStartDate(GregorianCalendar start_date) {
		this.start_date = start_date;
	}
	
	public int getId()
	{
		return id;
	}
	
	public void setID(int id)
	{
		this.id = id;
	}
	
	public void setTotal(double t)
	{
		this.total = t;
	}
	
	public void setBalance(double b)
	{
		this.balance = b;
	}
	
	
}
